package com.yenlien.traceyun.business.dao;

import com.alibaba.fastjson.JSONObject;
import com.yenlien.traceyun.business.entity.Equipment;
import com.yenlien.traceyun.webase.dao.BaseJpaDao;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
 * Created by LiuSha on 2020/7/28.
 */
public interface EquipmentDao extends BaseJpaDao<Equipment> {

    Equipment findById(String id);


    @Query(value = "SELECT * FROM t_equipment WHERE id IN (?1)", nativeQuery = true)
    List<Equipment> findInIdList(List<String> idList);

    List<Equipment> findByTypeAndCompanyId(String type, String companyId);



    List<Equipment> findByType(String type);


    @Query(value = "SELECT COUNT(id) FROM t_equipment WHERE type != 'VR'", nativeQuery = true)
    Long cntNotVr();

    @Query(value = "SELECT * FROM t_equipment WHERE `serial` IN (?1) AND company_id = ?2", nativeQuery = true)
    List<Equipment> findBySerialList(List<String> serialList, String companyId);

    Equipment findBySerial(String serial);

    List<Equipment> findByCompanyId(String companyId);

    @Query(value = "SELECT * FROM t_equipment WHERE company_id IN (?1) AND type <> 'VR'", nativeQuery = true)
    List<Equipment> findByCompanyIdList(List<String> idList);

    @Query(value = "SELECT * FROM t_equipment WHERE company_id = ?1 AND type <> '摄像机'", nativeQuery = true)
    List<Equipment> findDevListByCompanyId(String companyId);
    @Query(value = "SELECT * FROM t_equipment WHERE company_id = ?1 AND type = '摄像机'", nativeQuery = true)
    List<Equipment> findVideoListByCompanyId(String companyId);

    List<Equipment> findByAppKey(String appKey);


    @Query(value = "SELECT id FROM t_equipment WHERE (type = '土壤墒情' OR type = '气象传感') AND manufacturer = '世纪瑞通'", nativeQuery = true)
    List<String> findEnvSoil();


    Equipment findBySerialAndCompanyId(String serial, String compId);


    @Query(value = "SELECT * FROM t_equipment WHERE company_id IN (SELECT tc.id FROM t_company tc WHERE tc.parent_id = ?1) AND type = ?2", nativeQuery = true)
    List<Equipment> findByPCompanyIdAndType(String companyId, String type);

    @Query(value = "SELECT * FROM t_equipment WHERE company_id = ?1 AND type LIKE CONCAT('%', ?2,'%')", nativeQuery = true)
    List<Equipment> findByCompanyIdAndType(String companyId, String type);

    @Query(value = "SELECT * FROM t_equipment WHERE company_id = ?1 AND type = ?2 AND name LIKE CONCAT('%', ?3,'%')", nativeQuery = true)
    List<Equipment> findByCompanyIdAndTypeAndName(String companyId, String type, String name);

    @Query(value = "SELECT * FROM t_equipment WHERE company_id = ?1 AND name LIKE CONCAT('%', ?2,'%')", nativeQuery = true)
    List<Equipment> findByCompanyIdAndName(String companyId, String name);

    @Query(value = "select * from t_equipment where id in (?1)", nativeQuery = true)
    public List<Equipment> findById(List<String> ids);

    @Query(value = "select e from Equipment e where e.id in( select bse.equipmentId  from BaseSoilEquipment bse where bse.baseSoilId=?1)")
    public List<Equipment> findBySoil(String baseSoilId);


    @Query(value = "SELECT * FROM t_equipment WHERE company_id IN " +
            "(SELECT tc.id FROM t_company tc JOIN t_company_customapp_version tcv ON tc.id = tcv.company_id WHERE tc.parent_id = '2c9d0ded821a5f77018224e6b5920001' AND tcv.app_id = ?1)", nativeQuery = true)
    List<Equipment> getByAppId(String appId);

    @Query(value = "SELECT * FROM t_equipment WHERE company_id IN (SELECT tc.id FROM t_company tc WHERE tc.parent_id = '2c9d0ded821a5f77018224e6b5920001')", nativeQuery = true)
    List<Equipment> getByDeGe();


    @Query(value = "SELECT * FROM t_equipment WHERE company_id IN (SELECT id FROM t_company WHERE parent_id = ?1)", nativeQuery = true)
    List<Equipment> getByCompPatentId(String companyId);


    @Query(value = "SELECT * FROM t_equipment WHERE type = ?1 AND company_id IN (SELECT tc.id FROM t_company tc WHERE tc.parent_id = '2c9d0ded821a5f77018224e6b5920001')", nativeQuery = true)
    List<Equipment> getByDeGeAndType(String type);


    @Query(value = "select e from Equipment e where e.id in( select bse.equipmentId  from BaseSoilEquipment bse where bse.companyId=?1 and bse.appId=?2)")
    public List<Equipment> findByBind(String companyId, String appId);

    //  如果实体类里不只是要查询的字段是会报找不到某个字段的。 Column 'XXX' not found
//    @Query(value = "SELECT * FROM t_equipment", nativeQuery = true)
    List<Equipment> findByManufacturer(String manufacturer);

    List<Equipment> findByManufacturerAndType(String manufacturer, String type);

    // 西昌葡萄
    @Query(value = "SELECT * FROM t_equipment WHERE manufacturer = '新风向' AND company_id IN (SELECT id FROM t_company WHERE parent_id = '4028f5a177cc8abb0177cca275cd001e')", nativeQuery = true)
    List<Equipment> findXCXFX();

    @Query(value = "SELECT * FROM t_equipment WHERE manufacturer = '科百' AND company_id IN (SELECT id FROM t_company WHERE parent_id = '4028f5a177cc8abb0177cca275cd001e')", nativeQuery = true)
    List<Equipment> findXCKB();

    @Query(value = "SELECT te.*, tbse.massif_id FROM t_base_soil_equipment tbse RIGHT JOIN t_equipment te ON tbse.equipment_id = te.id WHERE manufacturer = ?1 AND te.type = ?2", nativeQuery = true)
    List<JSONObject> getEquipment(String manufacturer, String type);


    @Query(value = "SELECT te.*, tbse.massif_id FROM t_base_soil_equipment tbse JOIN t_equipment te ON tbse.equipment_id = te.id WHERE te.id IN (?1)", nativeQuery = true)
    List<Equipment> getEquipment(List<String> equipmentId);

    // 根据基地ID获取基地上的设备
    @Query(value = "SELECT id, type, `name`, `model`, `manufacturer`, `channel`, purchase_time purchaseTime, serial FROM t_equipment WHERE id IN (SELECT equipment_id FROM t_base_soil_equipment WHERE base_soil_id IN (SELECT id FROM t_base_soil WHERE type = '6' AND pid = ?1))", nativeQuery = true)
    List<JSONObject> findEquipment(String jiDiId);

    /**
     * 根据地块ID查询对应的设备
     *
     * @return
     */
    @Query(value = "SELECT te.* FROM t_equipment te WHERE te.id IN (SELECT equipment_id FROM t_base_soil_equipment WHERE base_soil_id = ?1)", nativeQuery = true)
    List<Equipment> getEquipmentByBaseSOilId(String baseSOilId);

    @Query(value = "SELECT * FROM t_equipment te WHERE EXISTS (SELECT tbse.id FROM t_base_soil_equipment tbse WHERE tbse.equipment_id = te.id AND tbse.massif_id = ?1)", nativeQuery = true)
    List<Equipment> getEquByMassifId(String massifId);

    @Query(value = "SELECT * FROM t_equipment WHERE company_id IN (SELECT tc.id FROM t_company tc JOIN t_company_customapp_version tcv ON tc.id = tcv.company_id WHERE tc.parent_id = '2c9d0ded821a5f77018224e6b5920001')", nativeQuery = true)
    List<Equipment> findEquipment();

}
